package com.stx.mapper;

import com.stx.bean.Car;
import com.stx.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*车辆管理读取数据库*/
public class CarMapper {
    //获取车辆列表
    public static List<Car> findCarList() throws SQLException {
        List<Car> carlist = new ArrayList<>();
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from car";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");   //获取id
            String carNumber = resultSet.getString("carNumber");
            String carOwner = resultSet.getString("carOwner");
            String phone = resultSet.getString("phone");
            Car car = new Car(id,carNumber, carOwner, phone);
            carlist.add(car);
        }
        return carlist;
    }

/*添加车辆*/
    public static Boolean carAdd(Car car) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        String sql = "insert into car(id,carNumber,carOwner,phone) value (?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,car.getId());
        preparedStatement.setString(2,car.getCarNumber());
        preparedStatement.setString(3,car.getCarOwner());
        preparedStatement.setString(4,car.getPhone());
        int i = preparedStatement.executeUpdate();
        return true;
    }
    //根据id查找车辆
    public static Car findCarById(String id) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from car where id = ? ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String carNumber = resultSet.getString("carNumber");
            String carOwner = resultSet.getString("carOwner");
            String phone = resultSet.getString("phone");
            Car car = new Car(id,carNumber, carOwner, phone);
            return car;
        }
        return null;
    }

    //更新车辆信息
    public static Boolean carUpdate(Car car) throws SQLException{
        Connection connection = JDBCUtil.getConnection();
        String sql = "update car set carNumber=?,carOwner=?,phone=? where id =?" ;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,car.getCarNumber());
        preparedStatement.setString(2,car.getCarOwner());
        preparedStatement.setString(3,car.getPhone());
        preparedStatement.setString(4,car.getId());
        int i = preparedStatement.executeUpdate();
        return true;
    }
    //根据id删除车辆
    public static Boolean delCarById(String id) throws Exception {
        Connection connection = JDBCUtil.getConnection();
        String sql = "delete from car where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,id);
        int i = preparedStatement.executeUpdate();
        return true;
    }
}
